package com.lawyers.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.lawyers.common.ComentarioVO;
import com.lawyers.common.DemandaVO;
import com.lawyers.common.DocumentoCompletoVO;
import com.lawyers.common.DocumentoVO;
import com.lawyers.common.UserVO;
import com.lawyers.form.BajarArchivoForm;

public class DBAcciones extends DBAction{
	public static List<DocumentoVO> getListaDocumentos(BajarArchivoForm form){
		try {
			String conexionString = MySQL.establecerConexion();
			List<DocumentoVO> listaDocumentos = new ArrayList<DocumentoVO>();
			if (conexionString.equalsIgnoreCase("OK")) {
				Connection connection = MySQL.getConnection();
				connection.setAutoCommit(true);
				String sql = "SELECT * FROM templates WHERE 1 = 1";
				
				if(form.getActividad() != null && !"".equals(form.getActividad()))
					sql += " AND `actividad` = " + form.getActividad();
				if(form.getCct() != null && !"".equals(form.getCct()))
					sql += " AND `cct` = " + form.getCct();
				if(form.getRama() != null && !"".equals(form.getRama()))
					sql += " AND `rama` = " + form.getRama();
				if(form.getCategoria() != null && !"".equals(form.getCategoria()))
					sql += " AND `categoria profesional` = " + form.getCategoria();
				if(form.getTipoDespido() != null && !"".equals(form.getTipoDespido()))
					sql += " AND `tipo de despido` = " + form.getTipoDespido();
				if(form.getDifSalarial() != null && !"".equals(form.getDifSalarial()))
					sql += " AND `diferencias salariales` = " + form.getDifSalarial();
				if(form.getInconstitucionalidad() != null && !"".equals(form.getInconstitucionalidad()))
					sql += " AND `Incostitucionalidad tope indem` = " + form.getInconstitucionalidad();
				if(form.getHsExtra() != null && !"".equals(form.getHsExtra()))
					sql += " AND `horas extras` = " + form.getHsExtra();
				if(form.getLey24013() != null && !"".equals(form.getLey24013()))
					sql += " AND `ley24013` = " + form.getLey24013();
				if(form.getArt125323() != null && !"".equals(form.getArt125323()))
					sql += " AND `Art1_25323` = " + form.getArt125323();
				if(form.get_132bis() != null && !"".equals(form.get_132bis()))
					sql += " AND `132_bis` = " + form.get_132bis();
				if(form.getSolidaridad() != null && !"".equals(form.getSolidaridad()))
					sql += " AND `Solidaridad` = " + form.getSolidaridad();
				if(form.getIusvariandi() != null && !"".equals(form.getIusvariandi()))
					sql += " AND `Ius variandi` = " + form.getIusvariandi();
				if(form.getTransfondos() != null && !"".equals(form.getTransfondos()))
					sql += " AND `Transferencia de fondo de comercio` = " + form.getTransfondos();
				if(form.getAcuerdoconc() != null && !"".equals(form.getAcuerdoconc()))
					sql += " AND `Acuerdo conciliatorio` = " + form.getAcuerdoconc();
				if(form.getAgravado() != null && !"".equals(form.getAgravado()))
					sql += " AND `Agravado por maternidad o matrimonio` = " + form.getAgravado();
				if(form.getIncorrecto() != null && !"".equals(form.getIncorrecto()))
					sql += " AND `Incorrecto encuadramiento convencional o de categoria` = " + form.getIncorrecto();
				if(form.getMonotributo() != null && !"".equals(form.getMonotributo()))
					sql += " AND `Monotributo` = " + form.getMonotributo();
				if(form.getDiferencia() != null && !"".equals(form.getDiferencia()))
					sql += " AND `Diferencias s/ liquidacion final` = " + form.getDiferencia();
				if(form.getInconstitucionalidad() != null && !"".equals(form.getInconstitucionalidad()))
					sql += " AND `Incostitucionalidad tope indem` = " + form.getInconstitucionalidad();
				if(form.getAccidentes() != null && !"".equals(form.getAccidentes()))
					sql += " AND `Accidente de trabajo` = " + form.getAccidentes();
				if(form.getContratacion() != null && !"".equals(form.getContratacion()))
					sql += " AND `Contratacion como eventual o plazo fijo` = " + form.getContratacion();
				if(form.getAsignacionesfam() != null && !"".equals(form.getAsignacionesfam()))
					sql += " AND `Asignaciones familiares` = " + form.getAsignacionesfam();
				if(form.getContratacionjor() != null && !"".equals(form.getContratacionjor()))
					sql += " AND `Contratacion como jornada reducida` = " + form.getContratacionjor();
				if(form.getSancion() != null && !"".equals(form.getSancion()))
					sql += " AND `Sanciones disciplinarias` = " + form.getSancion();
				if(form.getConducta() != null && !"".equals(form.getConducta()))
					sql += " AND `Conducta maliciosa` = " + form.getConducta();
				if(form.getDanio() != null && !"".equals(form.getDanio()))
					sql += " AND `Danio moral adicional` = " + form.getDanio();
				if(form.getProvincia() != null && !"".equals(form.getProvincia()))
					sql += " AND `ProvinciaBsAs` = " + form.getProvincia();
				if(form.getAcoso() != null && !"".equals(form.getAcoso()))
					sql += " AND `Acoso moral` = " + form.getAcoso();
				if(form.getBeneficio() != null && !"".equals(form.getBeneficio()))
					sql += " AND `Beneficio jubliatorio` = " + form.getBeneficio();
				if(form.getProyectada() != null && !"".equals(form.getProyectada()))
					sql += " AND `Proyectada por` = " + form.getProyectada();
				
				
				Statement stmt = connection.createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				while (rs.next()) {
					DocumentoVO doc = new DocumentoVO();
					doc.setId(Integer.toString(rs.getInt("id")));
					doc.setNombre(rs.getString("nombre"));
					doc.setPath(rs.getString("path"));
					if(rs.getString("type").equalsIgnoreCase(".doc") || rs.getString("type").equalsIgnoreCase(".docx"))
						doc.setTipo("Word");
					else if(rs.getString("type").equalsIgnoreCase(".pdf"))
						doc.setTipo("PDF");
					else
						doc.setTipo(rs.getString("type"));
					listaDocumentos.add(doc);
				}
				stmt.close();
			}
			return listaDocumentos;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return new ArrayList<DocumentoVO>();
		}
	}
	
	public static DocumentoVO getDocumento(String idDoc){
		try {
			String conexionString = MySQL.establecerConexion();
			DocumentoVO doc = null;
			if (conexionString.equalsIgnoreCase("OK")) {
				Connection connection = MySQL.getConnection();
				connection.setAutoCommit(true);
				String sql = "SELECT * FROM templates WHERE 1 = 1 AND id = ?";
				PreparedStatement pstmt = connection.prepareStatement(sql);
				pstmt.setInt(1, Integer.parseInt(idDoc));
				ResultSet rs = pstmt.executeQuery();
				while (rs.next()) {
					doc = new DocumentoVO();
					doc.setId(Integer.toString(rs.getInt("id")));
					doc.setNombre(rs.getString("nombre"));
					doc.setPath(rs.getString("path"));
					doc.setTipo(rs.getString("type"));
				}
				pstmt.close();
			}
			return doc;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	public static boolean subirTemplate(DocumentoVO doc){
		try {
			String conexionString = MySQL.establecerConexion();
			if (conexionString.equalsIgnoreCase("OK")) {
				Connection connection = MySQL.getConnection();
				connection.setAutoCommit(true);
				String sql = "INSERT INTO TEMPLATES(path,type,actividad,cct,rama,nombre,`categoria profesional`,`tipo de despido`,`diferencias salariales`,`horas extras`,ley24013,`Incostitucionalidad tope indem`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
				PreparedStatement pstmt = connection.prepareStatement(sql);
				pstmt.setString(1,doc.getPath());
				pstmt.setString(2,doc.getTipo());
				pstmt.setInt(3, doc.getActividad());
				pstmt.setInt(4, doc.getCct());
				pstmt.setInt(5, doc.getRama());
				pstmt.setString(6, doc.getNombre());
				pstmt.setInt(7, doc.getCategoria());
				pstmt.setInt(8, doc.getTipoDespido());
				pstmt.setInt(9, doc.getDifSalarial());
				pstmt.setInt(10, doc.getHsExtra());
				pstmt.setInt(11, doc.getLey24013());
				pstmt.setInt(12, doc.getInconstitucionalidad());
				
				pstmt.execute();
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		return false;
		
	}
	
	public static boolean subirDocumento(DocumentoCompletoVO doc){
		try {
			String conexionString = MySQL.establecerConexion();
			if (conexionString.equalsIgnoreCase("OK")) {
				Connection connection = MySQL.getConnection();
				connection.setAutoCommit(true);
				String sql = "INSERT INTO documentos_completos(path,type,nombre,autos,fecha,usuario,estado) VALUES (?,?,?,?,DATE(?),?,'A')";
				PreparedStatement pstmt = connection.prepareStatement(sql);
				pstmt.setString(1,doc.getPath());
				pstmt.setString(2,doc.getType());
				pstmt.setString(3, doc.getNombre());
				pstmt.setString(4, doc.getAutos());
				String fecha = doc.getFecha();
				String dia = fecha.substring(0, 2);
				String mes = fecha.substring(3,5);
				String anio = fecha.substring(6, 10);
				pstmt.setString(5, anio+'-'+mes+'-'+dia);
				pstmt.setInt(6, doc.getUsuario());
				pstmt.execute();
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		return false;
		
	}
	
	public static UserVO getUser(String idUser){
		try {
			String conexionString = MySQL.establecerConexion();
			UserVO user = null;
			if (conexionString.equalsIgnoreCase("OK")) {
				Connection connection = MySQL.getConnection();
				connection.setAutoCommit(true);
				String sql = "SELECT * FROM usuarios WHERE 1 = 1 AND id = ?";
				PreparedStatement pstmt = connection.prepareStatement(sql);
				pstmt.setInt(1, Integer.parseInt(idUser));
				ResultSet rs = pstmt.executeQuery();
				while (rs.next()) {
					user = new UserVO();
					user.setIdUsuario(rs.getInt("id"));
					user.setUsername(rs.getString("username"));
					user.setPassword(rs.getString("pass"));
					user.setPerfil(rs.getString("perfil"));
					user.setEstado(rs.getString("estado"));
				}
				pstmt.close();
			}
			return user;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	public static boolean insertarUsuario(UserVO user){
		try {
			String conexionString = MySQL.establecerConexion();
			if (conexionString.equalsIgnoreCase("OK")) {
				Connection connection = MySQL.getConnection();
				connection.setAutoCommit(true);
				if(user.getIdUsuario()==0){
					String sql = "INSERT INTO usuarios(username,pass,perfil,estado) VALUES (?,?,?,'A')";
					PreparedStatement pstmt = connection.prepareStatement(sql);
					pstmt.setString(1,user.getUsername());
					pstmt.setString(2,user.getPassword());
					pstmt.setString(3,user.getPerfil());
					pstmt.execute();
					return true;
				}else{
					if(!"".equalsIgnoreCase(user.getPassword())){
						String sql = "UPDATE usuarios SET username = ?, pass= ?, perfil = ? WHERE id = ?";
						PreparedStatement pstmt = connection.prepareStatement(sql);
						pstmt.setString(1,user.getUsername());
						pstmt.setString(2,user.getPassword());
						pstmt.setString(3,user.getPerfil());
						pstmt.setInt(4,user.getIdUsuario());
						pstmt.execute();
						return true;
					}else{
						String sql = "UPDATE usuarios SET username = ?, perfil = ? WHERE id = ?";
						PreparedStatement pstmt = connection.prepareStatement(sql);
						pstmt.setString(1,user.getUsername());
						pstmt.setString(2,user.getPerfil());
						pstmt.setInt(3,user.getIdUsuario());
						pstmt.execute();
						return true;
					}
					
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			if(user.getUsername()!=null)
				System.out.println("ERROR AL INSERTAR EL USUARIO: " + user.getUsername());
			else
				System.out.println("ERROR AL INSERTAR EL USUARIO");
			return false;
		}
		return false;
		
	}
	
	public static List<UserVO> getUsuarios(){
		try {
			String conexionString = MySQL.establecerConexion();
			List<UserVO> usuarios = new ArrayList<UserVO>();
			if (conexionString.equalsIgnoreCase("OK")) {
				Connection connection = MySQL.getConnection();
				connection.setAutoCommit(true);
				String sql = "SELECT * FROM usuarios";
				PreparedStatement pstmt = connection.prepareStatement(sql);
				ResultSet rs = pstmt.executeQuery();
				while (rs.next()) {
					UserVO user = new UserVO();
					user.setIdUsuario(rs.getInt("id"));
					user.setUsername(rs.getString("username"));
					user.setPassword(rs.getString("pass"));
					user.setPerfil(rs.getString("perfil"));
					user.setEstado(rs.getString("estado"));
					usuarios.add(user);
					
				}
				pstmt.close();
			}
			return usuarios;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	public static boolean eliminarUsuario(String idUsuario){
		try {
			String conexionString = MySQL.establecerConexion();
			if (conexionString.equalsIgnoreCase("OK")) {
				Connection connection = MySQL.getConnection();
				connection.setAutoCommit(true);
				String sql = "UPDATE usuarios SET estado = 'B' WHERE id = ?";
				PreparedStatement pstmt = connection.prepareStatement(sql);
				pstmt.setString(1,idUsuario);
				pstmt.execute();
				sql = "UPDATE documentos_completos SET estado = 'B' WHERE usuario = ?";
				pstmt = connection.prepareStatement(sql);
				pstmt.setString(1,idUsuario);
				pstmt.execute();
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		return false;
		
	}
	
	public static boolean habilitarUsuario(String idUsuario){
		try {
			String conexionString = MySQL.establecerConexion();
			if (conexionString.equalsIgnoreCase("OK")) {
				Connection connection = MySQL.getConnection();
				connection.setAutoCommit(true);
				String sql = "UPDATE usuarios SET estado = 'A' WHERE id = ?";
				PreparedStatement pstmt = connection.prepareStatement(sql);
				pstmt.setString(1,idUsuario);
				pstmt.execute();
				sql = "UPDATE documentos_completos SET estado = 'A' WHERE usuario = ?";
				pstmt = connection.prepareStatement(sql);
				pstmt.setString(1,idUsuario);
				pstmt.execute();
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		return false;
		
	}
	
	public static List<DocumentoCompletoVO> getDocumentosCompletosVO(){
		try {
			String conexionString = MySQL.establecerConexion();
			List<UserVO> usuarios = new ArrayList<UserVO>();
			if (conexionString.equalsIgnoreCase("OK")) {
				Connection connection = MySQL.getConnection();
				connection.setAutoCommit(true);
				String sql = "SELECT * FROM usuarios";
				PreparedStatement pstmt = connection.prepareStatement(sql);
				ResultSet rs = pstmt.executeQuery();
				while (rs.next()) {
					UserVO user = new UserVO();
					user.setIdUsuario(rs.getInt("id"));
					user.setUsername(rs.getString("username"));
					user.setPassword(rs.getString("pass"));
					user.setPerfil(rs.getString("perfil"));
					usuarios.add(user);
					
				}
				pstmt.close();
			}
			return null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	public static List<DemandaVO> getMisDemandas(int idUsuario){
		try {
			String conexionString = MySQL.establecerConexion();
			List<DemandaVO> demandas = new ArrayList<DemandaVO>();
			if (conexionString.equalsIgnoreCase("OK")) {
				Connection connection = MySQL.getConnection();
				connection.setAutoCommit(true);
				String sql = "SELECT autos,DATE_FORMAT(fecha, '%d/%m/%Y') fecha,id FROM documentos_completos WHERE usuario = ? ORDER BY autos,fecha";
				PreparedStatement pstmt = connection.prepareStatement(sql);
				pstmt.setInt(1,idUsuario);
				ResultSet rs = pstmt.executeQuery();
				while (rs.next()) {
					DemandaVO demanda = new DemandaVO();
					demanda.setAutos(rs.getString("autos"));
					demanda.setFecha(rs.getString("fecha"));
					demanda.setIdDemanda(rs.getInt("id"));
					demanda.setUsuario(idUsuario);
					demandas.add(demanda);
					
				}
				pstmt.close();
			}
			return demandas;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	public static List<DemandaVO> getDemanda(int idDemanda){
		try {
			String conexionString = MySQL.establecerConexion();
			List<DemandaVO> demandas = new ArrayList<DemandaVO>();
			if (conexionString.equalsIgnoreCase("OK")) {
				Connection connection = MySQL.getConnection();
				connection.setAutoCommit(true);
				String sql = "SELECT autos,DATE_FORMAT(fecha, '%d/%m/%Y') fecha,id,usuario FROM documentos_completos WHERE id = ?";
				PreparedStatement pstmt = connection.prepareStatement(sql);
				pstmt.setInt(1,idDemanda);
				ResultSet rs = pstmt.executeQuery();
				while (rs.next()) {
					DemandaVO demanda = new DemandaVO();
					demanda.setAutos(rs.getString("autos"));
					demanda.setFecha(rs.getString("fecha"));
					demanda.setIdDemanda(rs.getInt("id"));
					demanda.setUsuario(rs.getInt("usuario"));
					demandas.add(demanda);
				}
				pstmt.close();
			}
			return demandas;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	public static List<ComentarioVO> getComentarios(int idDemanda){
		try {
			String conexionString = MySQL.establecerConexion();
			List<ComentarioVO> comentarios = new ArrayList<ComentarioVO>();
			if (conexionString.equalsIgnoreCase("OK")) {
				Connection connection = MySQL.getConnection();
				connection.setAutoCommit(true);
				String sql = "SELECT id,idDocumento,observaciones,DATE_FORMAT(fecha, '%d/%m/%Y') fecha FROM demandaspordoc WHERE idDocumento = ? ORDER BY fecha,idDocumento";
				PreparedStatement pstmt = connection.prepareStatement(sql);
				pstmt.setInt(1,idDemanda);
				ResultSet rs = pstmt.executeQuery();
				while (rs.next()) {
					ComentarioVO comentario = new ComentarioVO();
					comentario.setObservaciones(rs.getString("observaciones"));
					comentario.setIdComentario(rs.getInt("id"));
					comentario.setIdDocumento(rs.getInt("idDocumento"));
					comentario.setFecha(rs.getString("fecha"));
					comentarios.add(comentario);
				}
				pstmt.close();
			}
			return comentarios;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	public static boolean insertarComentario(ComentarioVO comentario){
		try {
			String conexionString = MySQL.establecerConexion();
			if (conexionString.equalsIgnoreCase("OK")) {
				Connection connection = MySQL.getConnection();
				connection.setAutoCommit(true);
				String sql = "INSERT INTO demandaspordoc(idDocumento,fecha,observaciones) VALUES (?,DATE(?),?)";
				PreparedStatement pstmt = connection.prepareStatement(sql);
				pstmt.setInt(1,comentario.getIdDocumento());
				String fecha = comentario.getFecha();
				String dia = fecha.substring(0, 2);
				String mes = fecha.substring(3,5);
				String anio = fecha.substring(6, 10);
				pstmt.setString(2, anio+'-'+mes+'-'+dia);
				pstmt.setString(3,comentario.getObservaciones());
				pstmt.execute();
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		return false;
		
	}

}
